package com.bw.dao;

import com.bw.bean.AA;
import com.bw.bean.AB;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface AAdao {

    @Select("SELECT sum(amount) `value`FROM order_info WHERE create_time>='2023-01-06 00:00:00' and create_time<='2023-01-06 23:59:59'\n")
    double getlist();



    @Select("CALL kd")
    List<AA> getlist2();

    @Select("SELECT *,RANK() over(PARTITION by a.name) FROM (\n" +
            "SELECT \n" +
            "o.org_name name,\n" +
            "sum(actual_distance) distance,\n" +
            "count(o.id) num,\n" +
            "sum(TIMESTAMPDIFF(SECOND,actual_start_time,actual_end_time)) time,\n" +
            "sum(TIMESTAMPDIFF(SECOND,actual_start_time,actual_end_time))/count(o.id) avgtime,\n" +
            "sum(actual_distance)/count(o.id) avgdistance\n" +
            "FROM transport_task t \n" +
            "LEFT JOIN line_base_info l ON t.line_id=l.id \n" +
            "LEFT JOIN base_organ o ON l.org_id=o.id \n" +
            "GROUP BY o.org_name)a")
    List<AB> getlist3();

    @Select("SELECT r1.name,count(d.sorter_emp_id) value FROM transport_task_detail d \n" +
            "LEFT JOIN transport_task t ON d.transport_task_id=t.id \n" +
            "LEFT JOIN line_base_info l ON t.line_id=l.id \n" +
            "LEFT JOIN base_organ b ON l.org_id=b.id \n" +
            "LEFT JOIN base_region_info r ON b.region_id=r.id \n" +
            "LEFT JOIN base_region_info r1 on r.parent_id=r1.id\n" +
            "GROUP BY r1.name")
    List<AA> getlist4();

    @Select("SELECT b.name,count(o.id) value FROM order_info o \n" +
            "LEFT JOIN order_cargo d ON d.order_id=o.id \n" +
            "LEFT JOIN base_dic b ON d.cargo_type=b.id \n" +
            "GROUP BY b.name")
    List<AA> getlist5();
    @Select("SELECT r1.name,count(d.id) value FROM express_task_delivery d \n" +
            "LEFT JOIN base_organ b ON d.org_id=b.id \n" +
            "LEFT JOIN base_region_info r ON b.region_id=r.id \n" +
            "LEFT JOIN base_region_info r1 ON r.parent_id=r1.id \n" +
            "GROUP BY r1.name")
    List<AA> getlist6();

    @Select("SELECT '次数' name,count(*) value FROM transport_task \n" +
            "UNION\n" +
            "SELECT '公里' name,sum(actual_distance) value FROM transport_task \n" +
            "UNION\n" +
            "SELECT '时长' name,sum(TIMESTAMPDIFF(SECOND,actual_start_time,actual_end_time)) time FROM transport_task WHERE create_time>='2023-01-06 00:00:00' and create_time<='2023-01-06 23:59:59'")
    List<AA> getlist7();

    @Select("SELECT count(*) value FROM transport_task WHERE create_time>='2023-01-06 00:00:00' and create_time<='2023-01-06 23:59:59'\n")
    double getlist8();
}
